package com.web.notice.dao;

import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

import javax.annotation.Resource;

import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.web.notice.po.NoticePo;
import com.web.notice.service.NoticeService;
import com.web.system.po.UserPo;
import com.web.system.service.UserService;

@Service("noticeService")
@Transactional(rollbackFor = { Exception.class })
public class NoticeDAO implements NoticeService {

	@Resource 
	private SessionFactory sessionFactory;
	
	@Resource
	private UserService userService;
	
	public boolean saveOrUpdateNotice(NoticePo notice,int save , UserPo userPo) {
		// TODO Auto-generated method stub
		Session session = null;
      	try {
      		 session = this.sessionFactory.getCurrentSession();
      		 if(save == 0){
      			session.save(notice); 
      		 } else{
      			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
      			StringBuffer hql = new StringBuffer();
      			hql.append("update NoticePo set noticeAddTime='");
      			hql.append(sdf.format(notice.getNoticeAddTime()));
      			hql.append("', noticeAddUser='");
      			hql.append(notice.getNoticeAddUser());
      			hql.append("', noticeContent='");
      			hql.append(notice.getNoticeContent());
      			hql.append("', noticeOverdueTime='");
      			hql.append(sdf.format(notice.getNoticeOverdueTime()));
      			hql.append("', noticeTitle='"+notice.getNoticeTitle());
      			hql.append("', showState='");
      			hql.append(notice.getShowState());
      			hql.append("' where noticeId='");
      			hql.append(notice.getNoticeId());
      			hql.append("'");
      			session.createQuery(hql.toString()).executeUpdate(); 
      		 }
      		 
      		 return true;
      	} catch (Exception e) {
			// TODO: handle exception
      		e.printStackTrace();
      		throw new RuntimeException();
		} 
	}
	
	public int deleteNoticesByNoticeIds(String ids) {
		// TODO Auto-generated method stub
		Session session = null;
    	Query query = null;
    	String sql = "DELETE FROM NoticePo WHERE noticeId IN ("+ ids +")";
      	try {
      		 session = this.sessionFactory.getCurrentSession();
      		 query = session.createQuery(sql);
             return query.executeUpdate();
      	} catch (Exception e) {
      		e.printStackTrace();
      		throw new RuntimeException();
		}
	}

	public NoticePo getNoticeByNoticeId(Integer noticeId) {
		// TODO Auto-generated method stub
		Session session = null;
		Query query = null;
      	try {
      		session = this.sessionFactory.getCurrentSession();
  			StringBuffer hql = new StringBuffer();
    		hql.append("Select a From NoticePo a");
    		hql.append(" where a.noticeId = '");
    		hql.append(noticeId);
    		hql.append("'");
            query = session.createQuery(hql.toString());
            NoticePo noticePo = (NoticePo)query.list().get(0);
            return noticePo;
      	} catch (Exception e) {
			// TODO: handle exception
      		e.printStackTrace();
		} 
		return null;
	}
	
	@SuppressWarnings("unchecked")

	public List<NoticePo> getNoticeList(String queryString, int first,
			int pageSize) {
		// TODO Auto-generated method stub
		Session session = null;
    	Query query = null;
      	try {
      		 session = this.sessionFactory.getCurrentSession();
      		 SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); 
      		 String hql = "update NoticePo set showState = '3' where showState = '2' and noticeOverdueTime < '" + sdf.format(new Date()) + "'";
			 query = session.createQuery(hql);
      		 query.executeUpdate();
      		 query = session.createQuery(queryString);
             query.setFirstResult(first);
             query.setMaxResults(pageSize);
             List<NoticePo> noticePos = query.list(); 
             return noticePos;
      	} catch (Exception e) {
			// TODO: handle exception
      		e.printStackTrace();
      		
		} 
		return null;
	}

	public int getTotal(String sql) {
		// TODO Auto-generated method stub
		Session session = null;
    	Query query = null;
      	try {
      		 session = this.sessionFactory.getCurrentSession();
      		 String queryString = "select count(*) from NoticePo as notice where 1=1 " + sql;
      		 query = session.createQuery(queryString);       
      		 int count = ((Long)query.uniqueResult()).intValue();
      		 return count;
      	} catch (Exception e) {
			// TODO: handle exception
      		e.printStackTrace();
		} 
		return 0;
	}

	
	public int executeBySql(String sql){
		Session session = null;
    	Query query = null;
    	int count = 0;
      	try {
      		 session = this.sessionFactory.getCurrentSession();
      		 query = session.createQuery(sql);
      		 count = query.executeUpdate();
             return count;
      	} catch (Exception e) {
			// TODO: handle exception
      		e.printStackTrace();
      		throw new RuntimeException();
		} 
	}
	 
	@SuppressWarnings("unchecked")

	public List<NoticePo> getFirstPageNoticeList() {
		// TODO Auto-generated method stub
		Session session = null;
    	Query query = null;
      	try {
      		 session = this.sessionFactory.getCurrentSession();
      		 SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); 
      		 String hql = "update NoticePo set showState = '3' where showState = '2' and noticeOverdueTime < '" + sdf.format(new Date()) + "'";
			 query = session.createQuery(hql);
      		 query.executeUpdate();
      		 StringBuffer stringQuery = new StringBuffer();
      		 stringQuery.append("from NoticePo where showState = '2' and noticeOverdueTime >= '");
      		 stringQuery.append(sdf.format(new Date()));
      		 stringQuery.append("'");
      		 stringQuery.append(" order by noticeAddTime desc");
      		 query = session.createQuery(stringQuery.toString());
             List<NoticePo> noticePos = query.list(); 
             return noticePos;
      	} catch (Exception e) {
			// TODO: handle exception
      		e.printStackTrace();
		} 
		return null;
	}

	/**
	 * 审核不通过
	 */
	public int executeBySql(String sql, String ids , String showState) {
		// TODO Auto-generated method stub
		
		Session session = null;
    	Query query = null;
    	int count = 0;
      	try {
      		 session = this.sessionFactory.getCurrentSession();
      		 query = session.createQuery(sql);
      		 count = query.executeUpdate();
             return count;
      	} catch (Exception e) {
			// TODO: handle exception
      		e.printStackTrace();
      		throw new RuntimeException();
		} 
	}

	public boolean schedulerUpdate() {
		// TODO Auto-generated method stub
		Session session = null;
		SQLQuery query = null;
		try {
			session = this.sessionFactory.getCurrentSession();
			String sql = "update t_notice set showState = 3 where showState = 2 and now() > noticeOverdueTime";
			query = session.createSQLQuery(sql);
			query.executeUpdate();
			return true;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return false;
	}
}
